package com.piece.module.file.util.excel;

import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * access文件读取工具类
 */
@Slf4j
public class AccessUtil {

    /**
     * 读取access文件
     *
     * @param filePath 文件地址
     * @param tabName  标签名
     * @param hasTitle 是否读取标题行
     * @return
     */
    public static List<List<Object>> readACCESS(String filePath, String tabName, boolean hasTitle) {
        List<List<Object>> list = new ArrayList<>();
        Properties prop = new Properties();
        prop.put("charSet", "utf-8");
        prop.put("user", "");
        prop.put("password", "");
        String url;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            try {
                url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + filePath + ";DriverID=22;READONLY=FALSE;";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                conn = DriverManager.getConnection(url, prop);
                stmt = conn.createStatement();
            } catch (Exception e) {
                url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + filePath + ";DriverID=22;READONLY=FALSE;";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                conn = DriverManager.getConnection(url, prop);
                stmt = conn.createStatement();
            }
            rs = stmt.executeQuery("select * from " + tabName);
            ResultSetMetaData data = rs.getMetaData();
            int count = 0;
            List<Object> title = new ArrayList<>();
            while (rs.next()) {
                List<Object> row = new ArrayList<>();
                for (int i = 1; i <= data.getColumnCount(); i++) {
                    row.add(rs.getObject(i));
                    if (count == 0) {
                        String columnName = data.getColumnName(i);
                        title.add(columnName);
                        count++;
                    }
                }
                if (hasTitle && count == 0) {
                    list.add(title);
                }
                list.add(row);
            }
        } catch (Exception e) {
            log.error("读取Access文件异常：{}", e.getMessage());
        } finally {
            try {
                if (null != rs) rs.close();
                if (null != stmt) stmt.close();
                if (null != conn) conn.close();
            } catch (SQLException e) {
                log.error("关闭Access文件流异常：{}", e.getMessage());
            }
        }
        return list;
    }
}
